﻿/*-----------------------------------------------------------
 * Author : Tharinduni Udugamasuriya
 * QueryBuilder : Generate Query Text & Parameterized Query
 -----------------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DSPL.Contracts;
using DSPL.Contracts.Common;
using DSPL.Contracts.Interfaces;
using Microsoft.Practices.Unity;


namespace DSPL.DataAccess
{
    public class QueryBuilder
    {
        IMessageService messageService;

        public QueryBuilder()
        {
            IUnityContainer container = GlobalVariables.GlobalUnityContainer;
            if (container != null)
            {
                messageService = container.Resolve<IMessageService>();
            }
        }

        public string SelectQueryBuilder(Query QueryFormat)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                
                if (QueryFormat.QType == QueryType.NONE)
                {
                    messageService.ShowMessage(MessageText.QueryTypeCheck, MessageType.ErrOK);
                    return "";
                }

                if (QueryFormat.QType == QueryType.SELECTOTHER)
                {
                    if (QueryFormat.QueryString == "")
                    {
                        messageService.ShowMessage(MessageText.QueryStringCheck, MessageType.ErrOK);
                        return "";
                    }
                    sb.Append(QueryFormat.QueryString);
                    return sb.ToString();
                }

                if (QueryFormat.TableName == "" && QueryFormat.TableNameList == null)
                {
                    messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                    return "";
                }

                //SELECT
                sb.Append("SELECT ");


                //*, DISTINCT, COLUMNS, TOP N
                switch (QueryFormat.QType)
                {
                    case QueryType.SELECT:
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;

                    case QueryType.SELECTALL:
                        sb.Append("* ");
                        break;

                    case QueryType.SELECTTOPN:
                        sb.Append("TOP " + QueryFormat.NumberOfRecords + " ");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;

                    case QueryType.SELECTTOPNALL:
                        sb.Append("TOP " + QueryFormat.NumberOfRecords + " * ");
                        break;

                    case QueryType.SELECTDISTINCT:
                        sb.Append("DISTINCT  ");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;                                            
                }

                //Append Table Name
                if (QueryFormat.TableNameList != null)
                {
                    sb.Append(" FROM ");
                    foreach (string item in QueryFormat.TableNameList)
                    {
                        sb.Append(item + ", ");
                    }
                    sb.Remove(sb.Length - 2, 2);
                }
                else if (QueryFormat.TableName != "")
                    sb.Append(" FROM " + QueryFormat.TableName + " ");


                //Append Join Clause
                if (QueryFormat.JoinStructure != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    foreach (ColumnWithValue item in QueryFormat.JoinStructure)
                    {
                        string JoinString = "";
                        switch (item.JoinType)
                        {
                            case QJoinType.INNERJOIN: JoinString = "INNER JOIN"; break;
                            case QJoinType.FULLOUTERJOIN: JoinString = "FULL OUTER JOIN"; break;
                            case QJoinType.LEFTOUTERJOIN: JoinString = "LEFT OUTER JOIN"; break;
                            case QJoinType.RIGHTOUTERJOIN: JoinString = "RIGHT OUTER JOIN"; break;
                            case QJoinType.CROSSJOIN: JoinString = "CROSS JOIN"; break;
                        }


                        sb1.Append(" " + JoinString + " " + item.ToTable + " ON " + GetEquation(item.Comparison, item.FromTable + "." + item.FromColumn, item.ToTable + "." + item.ToColumn));
                    }

                    sb.Append(sb1.ToString());
                }

                //Append Where Clause
                if (QueryFormat.WhereClause != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    int level = 0;
                    foreach (ColumnWithValue item in QueryFormat.WhereClause)
                    {
                        if (sb1.ToString().Length == 0)
                        {
                            sb1.Append("(");
                            if (item.Level > 0)
                                for (int i = item.Level; i > 0; i--)
                                    sb1.Append("( ");
                        }
                        else
                        {
                            if (item.Level > level)
                            {
                                sb1.Append(" " + item.ColumnConnector + " ");
                                sb1.Append("(");
                            }
                            else if (item.Level < level)
                            {
                                sb1.Append(")");
                                sb1.Append(" " + item.ColumnConnector + " ");
                            }
                            else
                                sb1.Append(" " + item.ColumnConnector + " ");
                        }

                        sb1.Append(GenerateWhereClause(item.ColumnName, item.ColumnValue, item.Comparison));

                        level = item.Level;

                    }

                    if (level > 0)
                        for (int i = level; i > 0; i--)
                            sb1.Append(") ");

                    if (sb1.ToString().Length > 0)
                    {
                        sb1.Append(") ");
                        sb.Append(" WHERE " + sb1.ToString());
                    }
                }

                //Append GROUP BY
                if (QueryFormat.Groupby != null)
                {
                    sb.Append(" GROUP BY ");
                    foreach (ColumnWithValue item in QueryFormat.Groupby)
                    {
                        sb.Append(item.ColumnName);
                        sb.Append(", ");
                    }
                    sb.Remove(sb.Length - 2, 2);
                }

                //Append HAVING
                if (QueryFormat.HavingClause != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    int level = 0;
                    foreach (ColumnWithValue item in QueryFormat.HavingClause)
                    {
                        if (sb1.ToString().Length == 0)
                        {
                            sb1.Append("(");
                            if (item.Level > 0)
                                for (int i = item.Level; i > 0; i--)
                                    sb1.Append("( ");
                        }
                        else
                        {
                            if (item.Level > level)
                            {
                                sb1.Append(" " + item.ColumnConnector + " ");
                                sb1.Append("(");
                            }
                            else if (item.Level < level)
                            {
                                sb1.Append(")");
                                sb1.Append(" " + item.ColumnConnector + " ");
                            }
                            else
                                sb1.Append(" " + item.ColumnConnector + " ");
                        }

                        sb1.Append(GenerateWhereClause(item.ColumnName, item.ColumnValue, item.Comparison));

                        level = item.Level;

                    }

                    if (level > 0)
                        for (int i = level; i > 0; i--)
                            sb1.Append(") ");

                    if (sb1.ToString().Length > 0)
                    {
                        sb1.Append(") ");
                        sb.Append(" HAVING " + sb1.ToString());
                    }
                }

                //Append ORDER BY
                if (QueryFormat.OrderbyClause != null)
                {
                    sb.Append(" ORDER BY ");
                    foreach (ColumnWithValue item in QueryFormat.OrderbyClause)
                    {
                        sb.Append(item.ColumnName);
                        switch (item.OrderBy)
                        {
                            case QOrderby.ASCENDING:
                                sb.Append(" ASC");
                                break;

                            case QOrderby.DESCENDING:
                                sb.Append(" DESC");
                                break;

                            default:
                                break;
                        }
                        sb.Append(", ");
                    }
                    sb.Remove(sb.Length - 2, 2);
                }

                return sb.ToString();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public object SelectQueryBuilder(Query QueryFormat, ref SqlCommand sqlCommand)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                DbParameter parameter;

                if (QueryFormat.QType == QueryType.NONE)
                {
                    messageService.ShowMessage(MessageText.QueryTypeCheck, MessageType.ErrOK);
                    return null;
                }

                if (QueryFormat.QType == QueryType.SELECTOTHER)
                {
                    if (QueryFormat.QueryString == "")
                    {
                        messageService.ShowMessage(MessageText.QueryStringCheck, MessageType.ErrOK);
                        return null;
                    }
                    sb.Append(QueryFormat.QueryString);

                    if (QueryFormat.ParameterList != null)
                    {
                        foreach (Parameter item in QueryFormat.ParameterList)
                        {
                            parameter = sqlCommand.CreateParameter();
                            parameter.ParameterName = item.ParamName;
                            parameter.Value = item.ParamValue;
                            sqlCommand.Parameters.Add(parameter);
                        }
                    }
                    sqlCommand.CommandText = sb.ToString();

                    return sqlCommand;
                }

                if (QueryFormat.TableName == "" && QueryFormat.TableNameList == null)
                {
                    messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                    return null;
                }
                                
                //SELECT
                sb.Append("SELECT ");


                //*, DISTINCT, COLUMNS, TOP N
                switch (QueryFormat.QType)
                {
                    case QueryType.SELECT:
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;

                    case QueryType.SELECTALL:
                        sb.Append("* ");
                        break;

                    case QueryType.SELECTTOPN:
                        sb.Append("TOP " + QueryFormat.NumberOfRecords + " ");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;

                    case QueryType.SELECTTOPNALL:
                        sb.Append("TOP " + QueryFormat.NumberOfRecords + " * ");
                        break;

                    case QueryType.SELECTDISTINCT:
                        sb.Append("DISTINCT  ");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;
                }

                //Append Table Name
                if (QueryFormat.TableNameList != null)
                {
                    sb.Append(" FROM ");
                    foreach (string item in QueryFormat.TableNameList)
                    {
                        sb.Append(item + ", ");
                    }
                    sb.Remove(sb.Length - 2, 2);
                }
                else if (QueryFormat.TableName != "")
                    sb.Append(" FROM " + QueryFormat.TableName + " ");

                //Append Join Clause
                if (QueryFormat.JoinStructure != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    foreach (ColumnWithValue item in QueryFormat.JoinStructure)
                    {
                        string JoinString = "";
                        switch (item.JoinType)
                        {
                            case QJoinType.INNERJOIN: JoinString = " INNER JOIN"; break;
                            case QJoinType.FULLOUTERJOIN: JoinString = " FULL OUTER JOIN"; break;
                            case QJoinType.LEFTOUTERJOIN: JoinString = " LEFT OUTER JOIN"; break;
                            case QJoinType.RIGHTOUTERJOIN: JoinString = " RIGHT OUTER JOIN"; break;
                            case QJoinType.CROSSJOIN: JoinString = " CROSS JOIN"; break;
                        }


                        sb1.Append(" " + JoinString + " " + item.ToTable + " ON " + GetEquation(item.Comparison, item.FromTable + "." + item.FromColumn, item.ToTable + "." + item.ToColumn));
                    }

                    sb.Append(sb1.ToString());
                }

                //Append Where Clause
                if (QueryFormat.WhereClause != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    int level=0;
                    foreach (ColumnWithValue item in QueryFormat.WhereClause)
                    {
                        if (sb1.ToString().Length == 0)
                        {
                            sb1.Append("(");
                            if (item.Level > 0)
                                for (int i = item.Level; i > 0; i--)
                                    sb1.Append("( ");
                        }
                        else
                        {
                            if (item.Level > level)
                            {
                                sb1.Append(" " + item.ColumnConnector + " ");
                                sb1.Append("(");
                            }
                            else if (item.Level < level)
                            {
                                sb1.Append(")");
                                sb1.Append(" " + item.ColumnConnector + " ");
                            }
                            else
                                sb1.Append(" " + item.ColumnConnector + " ");
                        }
                        string parameterName = string.Format("@p{0}_{1}", sqlCommand.Parameters.Count + 1, item.ColumnName.Replace('.', '_'));
                                  
                        sb1.Append(GetEquation(item.Comparison, item.ColumnName, parameterName));

                        parameter = sqlCommand.CreateParameter();
                        parameter.ParameterName = parameterName;
                        parameter.Value = item.ColumnValue;
                        sqlCommand.Parameters.Add(parameter);
                        level = item.Level;

                    }

                    if(level>0)
                        for (int i = level; i > 0; i--)
                            sb1.Append(") ");

                    if (sb1.ToString().Length > 0)
                    {
                        sb1.Append(") ");
                        sb.Append(" WHERE " + sb1.ToString());
                    }
                    else
                        sqlCommand.Parameters.Clear();
                }

                //Append GROUP BY
                if (QueryFormat.Groupby != null)
                {
                    sb.Append(" GROUP BY ");
                    foreach (ColumnWithValue item in QueryFormat.Groupby)
                    {
                        sb.Append(item.ColumnName);
                        sb.Append(", ");
                    }
                    sb.Remove(sb.Length - 2, 2);
                }

                //Append HAVING
                if (QueryFormat.HavingClause != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    int level = 0;
                    int paramCount = sqlCommand.Parameters.Count;
                    foreach (ColumnWithValue item in QueryFormat.HavingClause)
                    {
                        if (sb1.ToString().Length == 0)
                        {
                            sb1.Append("(");
                            if (item.Level > 0)
                                for (int i = item.Level; i > 0; i--)
                                    sb1.Append("( ");
                        }
                        else
                        {
                            if (item.Level > level)
                            {
                                sb1.Append(" " + item.ColumnConnector + " ");
                                sb1.Append("(");
                            }
                            else if (item.Level < level)
                            {
                                sb1.Append(")");
                                sb1.Append(" " + item.ColumnConnector + " ");
                            }
                            else
                                sb1.Append(" " + item.ColumnConnector + " ");
                        }
                        string parameterName = string.Format("@p{0}_{1}", sqlCommand.Parameters.Count + 1, item.ColumnName.Replace('.', '_'));

                        sb1.Append(GetEquation(item.Comparison, item.ColumnName, parameterName));

                        parameter = sqlCommand.CreateParameter();
                        parameter.ParameterName = parameterName;
                        parameter.Value = item.ColumnValue;
                        sqlCommand.Parameters.Add(parameter);
                        level = item.Level;

                    }

                    if (level > 0)
                        for (int i = level; i > 0; i--)
                            sb1.Append(") ");

                    if (sb1.ToString().Length > 0)
                    {
                        sb1.Append(") ");
                        sb.Append(" HAVING " + sb1.ToString());
                    }
                    else
                        if (paramCount > sqlCommand.Parameters.Count)
                            for (int i = paramCount; i < sqlCommand.Parameters.Count; i++)
                                sqlCommand.Parameters.RemoveAt(i);
                    
                }

                //Append ORDER BY
                if (QueryFormat.OrderbyClause != null)
                {
                    sb.Append(" ORDER BY ");
                    foreach (ColumnWithValue item in QueryFormat.OrderbyClause)
                    {
                        sb.Append(item.ColumnName);
                        switch (item.OrderBy)
                        {
                            case QOrderby.ASCENDING:
                                sb.Append(" ASC");
                                break;

                            case QOrderby.DESCENDING:
                                sb.Append(" DESC");
                                break;

                            default:
                                break;
                        }
                        sb.Append(", ");
                    }
                    sb.Remove(sb.Length - 2, 2);
                }


                sqlCommand.CommandText = sb.ToString();
                return sqlCommand;

            }
            catch (Exception)
            {

                throw;
            }
        }

        public string IUDQueryBuilder(Query QueryFormat)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                switch (QueryFormat.QType)
                {
                    case QueryType.INSERT:
                        if (QueryFormat.QType == QueryType.NONE)
                        {
                            messageService.ShowMessage(MessageText.QueryTypeCheck, MessageType.ErrOK);
                            return "";
                        }

                        if (QueryFormat.TableName == "")
                        {
                            messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                            return "";
                        }

                        sb.Append("INSERT INTO " + QueryFormat.TableName + "  (");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }

                        sb.Remove(sb.Length - 2, 2);
                        sb.Append(") VALUES (");

                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            switch (item.ColumnValue.GetType().Name)
                            {
                                case "String": sb.Append("'" + item.ColumnValue + "'"); break;
                                case "DateTime": sb.Append("'" + item.ColumnValue + "'"); break;
                                case "Boolean": sb.Append(item.ColumnValue); break;
                                case "SqlLiteral": sb.Append(item.ColumnValue); break;
                                default: sb.Append(item.ColumnValue); break;
                            }
                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        sb.Append(")");
                        break;

                    case QueryType.UPDATE:
                        if (QueryFormat.QType == QueryType.NONE)
                        {
                            messageService.ShowMessage(MessageText.QueryTypeCheck, MessageType.ErrOK);
                            return "";
                        }

                        if (QueryFormat.TableName == "")
                        {
                            messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                            return "";
                        }
                        sb.Append("UPDATE " + QueryFormat.TableName + " SET ");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            string valueString = "";
                            switch (item.ColumnValue.GetType().Name)
                            {
                                case "String": valueString = "'" + item.ColumnValue + "'"; break;
                                case "DateTime": valueString = "'" + item.ColumnValue + "'"; break;
                                case "Boolean": valueString = item.ColumnValue.ToString(); break;
                                case "SqlLiteral": valueString = item.ColumnValue.ToString(); break;
                                default: valueString = item.ColumnValue.ToString(); break;
                            }
                            sb.Append(GetEquation(item.Comparison, item.ColumnName, valueString));
                            sb.Append(", ");

                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;

                    case QueryType.DELETE:
                        if (QueryFormat.TableName == "")
                        {
                            messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                            return "";
                        }
                        sb.Append("DELETE FROM " + QueryFormat.TableName + " ");
                        break;

                    case QueryType.OTHER:
                        if (QueryFormat.QueryString == "")
                        {
                            messageService.ShowMessage(MessageText.QueryStringCheck, MessageType.ErrOK);
                            return "";
                        }
                        sb.Append(QueryFormat.QueryString);
                        break;
                }

                if (QueryFormat.WhereClause != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    int level = 0;
                    foreach (ColumnWithValue item in QueryFormat.WhereClause)
                    {
                        if (sb1.ToString().Length == 0)
                        {
                            sb1.Append("(");
                            if (item.Level > 0)
                                for (int i = item.Level; i > 0; i--)
                                    sb1.Append("( ");
                        }
                        else
                        {
                            if (item.Level > level)
                            {
                                sb1.Append(" " + item.ColumnConnector + " ");
                                sb1.Append("(");
                            }
                            else if (item.Level < level)
                            {
                                sb1.Append(")");
                                sb1.Append(" " + item.ColumnConnector + " ");
                            }
                            else
                                sb1.Append(" " + item.ColumnConnector + " ");
                        }

                        sb1.Append(GenerateWhereClause(item.ColumnName, item.ColumnValue, item.Comparison));

                        level = item.Level;

                    }

                    if (level > 0)
                        for (int i = level; i > 0; i--)
                            sb1.Append(") ");

                    if (sb1.ToString().Length > 0)
                    {
                        sb1.Append(") ");
                        sb.Append(" WHERE " + sb1.ToString());
                    }
                }

                return sb.ToString();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public object IUDQueryBuilder(Query QueryFormat, ref SqlCommand sqlCommand)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                DbParameter parameter;

                switch (QueryFormat.QType)
                {
                    case QueryType.INSERT:
                        if (QueryFormat.QType == QueryType.NONE)
                        {
                            messageService.ShowMessage(MessageText.QueryTypeCheck, MessageType.ErrOK);
                            return null;
                        }

                        if (QueryFormat.TableName == "")
                        {
                            messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                            return null;
                        }

                        sb.Append("INSERT INTO " + QueryFormat.TableName + "  (");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            sb.Append(item.ColumnName);
                            sb.Append(", ");
                        }

                        sb.Remove(sb.Length - 2, 2);
                        sb.Append(") VALUES (");

                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            string parameterName = string.Format("@p{0}_{1}", sqlCommand.Parameters.Count + 1, item.ColumnName.Replace('.', '_'));

                            sb.Append(parameterName);

                            parameter = sqlCommand.CreateParameter();
                            parameter.ParameterName = parameterName;
                            parameter.Value = item.ColumnValue;
                            sqlCommand.Parameters.Add(parameter);

                            sb.Append(", ");
                        }
                        sb.Remove(sb.Length - 2, 2);
                        sb.Append(")");
                        break;

                    case QueryType.UPDATE:
                        if (QueryFormat.QType == QueryType.NONE)
                        {
                            messageService.ShowMessage(MessageText.QueryTypeCheck, MessageType.ErrOK);
                            return null;
                        }

                        if (QueryFormat.TableName == "")
                        {
                            messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                            return null;
                        }
                        sb.Append("UPDATE " + QueryFormat.TableName + " SET ");
                        foreach (ColumnWithValue item in QueryFormat.ColValues)
                        {
                            string parameterName = string.Format("@p{0}_{1}", sqlCommand.Parameters.Count + 1, item.ColumnName.Replace('.', '_'));

                            sb.Append(item.ColumnName + " = " + parameterName);
                            sb.Append(", ");

                            parameter = sqlCommand.CreateParameter();
                            parameter.ParameterName = parameterName;
                            parameter.Value = item.ColumnValue;
                            sqlCommand.Parameters.Add(parameter);

                        }
                        sb.Remove(sb.Length - 2, 2);
                        break;

                    case QueryType.DELETE:
                        if (QueryFormat.TableName == "")
                        {
                            messageService.ShowMessage(MessageText.TableNameCheck, MessageType.ErrOK);
                            return null;
                        }
                        sb.Append("DELETE FROM " + QueryFormat.TableName + " ");
                        break;

                    case QueryType.OTHER:
                        if (QueryFormat.QueryString == "")
                        {
                            messageService.ShowMessage(MessageText.QueryStringCheck, MessageType.ErrOK);
                            return null;
                        }
                        sb.Append(QueryFormat.QueryString);

                        if (QueryFormat.ParameterList != null)
                        {
                            foreach (Parameter item in QueryFormat.ParameterList)
                            {
                                parameter = sqlCommand.CreateParameter();
                                parameter.ParameterName = item.ParamName;
                                parameter.Value = item.ParamValue;
                                sqlCommand.Parameters.Add(parameter);
                            }
                        }
                        break;
                }

                //Append Where Clause
                if (QueryFormat.WhereClause != null)
                {
                    StringBuilder sb1 = new StringBuilder();
                    int level = 0;
                    int paramCount = sqlCommand.Parameters.Count;
                    foreach (ColumnWithValue item in QueryFormat.WhereClause)
                    {
                        if (sb1.ToString().Length == 0)
                        {
                            sb1.Append("(");
                            if (item.Level > 0)
                                for (int i = item.Level; i > 0; i--)
                                    sb1.Append("( ");
                        }
                        else
                        {
                            if (item.Level > level)
                            {
                                sb1.Append(" " + item.ColumnConnector + " ");
                                sb1.Append("(");
                            }
                            else if (item.Level < level)
                            {
                                sb1.Append(")");
                                sb1.Append(" " + item.ColumnConnector + " ");
                            }
                            else
                                sb1.Append(" " + item.ColumnConnector + " ");
                        }
                        string parameterName = string.Format("@p{0}_{1}", sqlCommand.Parameters.Count + 1, item.ColumnName.Replace('.', '_'));

                        sb1.Append(GetEquation(item.Comparison, item.ColumnName, parameterName));

                        parameter = sqlCommand.CreateParameter();
                        parameter.ParameterName = parameterName;
                        parameter.Value = item.ColumnValue;
                        sqlCommand.Parameters.Add(parameter);
                        level = item.Level;

                    }

                    if (level > 0)
                        for (int i = level; i > 0; i--)
                            sb1.Append(") ");

                    if (sb1.ToString().Length > 0)
                    {
                        sb1.Append(") ");
                        sb.Append(" WHERE " + sb1.ToString());
                    }
                    else
                        if (paramCount > sqlCommand.Parameters.Count)
                            for (int i = paramCount; i < sqlCommand.Parameters.Count; i++)
                                sqlCommand.Parameters.RemoveAt(i);
                }

                sqlCommand.CommandText = sb.ToString();
                return sqlCommand;

            }
            catch (Exception)
            {

                throw;
            }
        }

        private string GetEquation(QComparison comparisson, string leftValue, string rightValue)
        {
            string Output = "";
            switch (comparisson)
            {
                case QComparison.EQUALS:
                    Output = leftValue + " = " + rightValue; break;
                case QComparison.NOTEQUAL:
                    Output = leftValue + " <> " + rightValue; break;
                case QComparison.GREATERTHAN:
                    Output = leftValue + " > " + rightValue; break;
                case QComparison.GREATERTHANEQUAL:
                    Output = leftValue + " >= " + rightValue; break;
                case QComparison.LESSTHAN:
                    Output = leftValue + " < " + rightValue; break;
                case QComparison.LESSTHANEQUAL:
                    Output = leftValue + " <= " + rightValue; break;
                case QComparison.LIKE:
                    Output = leftValue + " LIKE " + rightValue; break;
                case QComparison.NOTLIKE:
                    Output = "NOT " + leftValue + " LIKE " + rightValue; break;
                case QComparison.IN:
                    Output = leftValue + " IN (" + rightValue + ")"; break;
                case QComparison.NOTIN:
                    Output = leftValue + " NOT IN (" + rightValue + ")"; break;
            }
            return Output;
        }

        private string GenerateWhereClause(string columnName, object columnValue, QComparison columnComparison)
        {
            string Output = "";

            switch (columnValue.GetType().Name)
            {
                case "String":
                    columnValue = " '" + columnValue + "' ";
                    break;

                case "DateTime":
                    columnName = "CONVERT(datetime,CONVERT(varchar(max)," + columnName + ",101)) ";
                    columnValue = "CONVERT(datetime,CONVERT(varchar(max)," + columnValue + ",101)) ";
                    break;

                case "DBNull":
                    columnValue = DBNull.Value;
                    break;
            }

            switch (columnComparison)
            {
                case QComparison.EQUALS:
                    Output = columnName + " = " + columnValue; break;
                case QComparison.NOTEQUAL:
                    Output = columnName + " <> " + columnValue; break;
                case QComparison.GREATERTHAN:
                    Output = columnName + " > " + columnValue; break;
                case QComparison.GREATERTHANEQUAL:
                    Output = columnName + " >= " + columnValue; break;
                case QComparison.LESSTHAN:
                    Output = columnName + " < " + columnValue; break;
                case QComparison.LESSTHANEQUAL:
                    Output = columnName + " <= " + columnValue; break;
                case QComparison.LIKE:
                    Output = columnName + " LIKE " + columnValue; break;
                case QComparison.NOTLIKE:
                    Output = "NOT " + columnName + " LIKE " + columnValue; break;
                case QComparison.IN:
                    Output = columnName + " IN (" + columnValue + ")"; break;
                case QComparison.NOTIN:
                    Output = columnName + " NOT IN (" + columnValue + ")"; break;
            }
            return Output;
        }

        
    }
}
